<?php
define("dbpath", $_SERVER['DOCUMENT_ROOT']."\RecJP\DB\sqlitedb");
//define("dbpath","e:\php5\Mywwwroot\RecJP\DB\sqlitedb");
define("dsn", "sqlite:".dbpath);
class SqliteHelper
{
	public function __construct(){
		//echo dbpath."<br>";
	}
	//$Parameter为数组参数，格式如下$p=array(":name"=>"Mike"）
	public function Query($SQL,$Parameter)
	{ 
		try
		{
			$pdo = new PDO(dsn);
			if ($pdo){
				//echo 'OK！SQLite 连接成功！<br />';
			}else{
				echo 'Error！SQLite 连接失败，请检查！<br />';
			}
			$PDOStatement = $pdo->prepare($SQL); 
			if($Parameter!=null)
			{
				while($key= key($Parameter)) 
				{
					$PDOStatement->bindValue($key,$Parameter[$key]); 
					next($Parameter);
				}
			}
			$PDOStatement->setFetchMode(PDO::FETCH_ASSOC);
			$PDOStatement->execute();
			
			return $PDOStatement->fetchAll();
		}
		catch(PDOException $e){
			die("Error!: ".$e->getMessage()."<br/>");
		}
	}
	
	public function ExecNoTans($SQL,$Parameter)
	{ 
		try
		{
			$pdo = new PDO(dsn);
			if ($pdo){
				//echo 'OK！SQLite 连接成功！<br />';
			}else{
				echo 'Error！SQLite 连接失败，请检查！<br />';
			}
			$PDOStatement = $pdo->prepare($SQL); 
			if($Parameter!=null)
			{
				while($key= key($Parameter)) 
				{
					$PDOStatement->bindValue($key,$Parameter[$key]); 
					next($Parameter);
				}
			}
			$pdo=null;
			return $PDOStatement->execute();
		}
		catch(PDOException $e){
			die("Error!: ".$e->getMessage()."<br/>");
		}
	}
	
	//$commit=-1表示回滚
	//$commit=0不回滚，不提交
	//$commit=1表示提交
	//调用例子如下：返回的pdo作为下一次调用的输入参数。最后一个sql语句，$commit设为1
	// $p=array(":word"=>"444",":read"=>"cccc",":meaning"=>"221",":lesson"=>"12");
	// $pdo=$s->ExecWithTans(null,"insert into danci (word,read,meaning,lesson) values(:word,:read,:meaning,:lesson)",$p,0);

	// $p=array(":word"=>"555",":read"=>"cccc",":meaning"=>"221",":lesson"=>"12");
	// $pdo=$s->ExecWithTans($pdo,"insert into danci (word,read,meaning,lesson) values(:word,:read,:meaning,:lesson)",$p,0);

	// $p=array(":word"=>"666",":read"=>"cccc",":meaning"=>"221",":lesson"=>"12");
	// $pdo=$s->ExecWithTans($pdo,"insert into danci (word,read,meaning,lesson) values(:word,:read,:meaning,:lesson)",$p,1);
	public function ExecWithTans($pdo,$SQL,$Parameter,$commit)
	{ 
		try
		{
			if($pdo==null)
			{
				$pdo = new PDO(dsn);
				$pdo->beginTransaction();
			}
			
			if ($pdo){
				echo 'OK！SQLite 连接成功！<br />';
			}else{
				echo 'Error！SQLite 连接失败，请检查！<br />';
			}
			
			$PDOStatement = $pdo->prepare($SQL); 
			if($Parameter!=null)
			{
				while($key= key($Parameter)) 
				{
					$PDOStatement->bindValue($key,$Parameter[$key]); 
					next($Parameter);
				}
			}
			$PDOStatement->execute();
			if($commit==1)
			{
				$pdo->commit(); 
				$pdo=null;
			}
			else if($commit==-1)
			{
				$pdo->rollBack(); 
				$pdo=null;
			}
			return $pdo;
		}
		catch(PDOException $e){
			$pdo->rollBack(); 
			die("Error!: ".$e->getMessage()."<br/>");
		}
	}
}

